import sqlite3
import time
import cPickle
import ajc

tables = {}
for table in ['main', 'main_in', 'split', 'split_in']:
    tables[table] = sqlite3.connect(table + '.sqlite3')
    if table[0:4] == 'main':
        tables[table].execute("CREATE TABLE IF NOT EXISTS " + table + " (fid INTEGER, key TEXT, value TEXT, PRIMARY KEY (fid, key))")
    else:
        tables[table].execute("CREATE TABLE IF NOT EXISTS " + table + " (fid INTEGER, key TEXT, obj TEXT, str TEXT, num NUMERIC, PRIMARY KEY (fid, key))")
    
    if table == 'main_in':
        tables[table].execute("CREATE INDEX main_in_value_pin ON main_in (value)")
    elif table[-3:] == '_in':
        tables[table].execute("CREATE INDEX " + table + "_obj_pin ON " + table + " (obj)")
        tables[table].execute("CREATE INDEX " + table + "_str_pin ON " + table + " (str)")
        tables[table].execute("CREATE INDEX " + table + "_num_pin ON " + table + " (num)")

for table in ['main_in', 'split', 'split_in', 'main']: # + ['main', 'split', 'split_in', 'main_in']:
    db = tables[table]
    time.sleep(1)
    ajc.util.time_start(msg = table + ' insert')
    for i in range(10000):
        for j in range(4):
            if j == 0:
                value = 'sAndrew' + str(i) if table[0:4] == 'main' else 'Andrew' + str(i)
            elif j == 1:
                value = 'f' + str(i/10 + 10.234) if table[0:4] == 'main' else i/10 + 10.234
            elif j == 2:
                value = 'i' + str(j + i % 100 + 32) if table[0:4] == 'main' else j + i % 100 + 32
            elif j == 3:
                value = 'x1323' + cPickle.dumps([i, j, 's'])
                
            if table[0:4] == 'main':
                db.execute("INSERT INTO " + table + " (fid,key,value) VALUES (?,?,?)", (4*i+j, 'hello', value,))
            elif j == 0:
                db.execute("INSERT INTO " + table + " (fid,key,obj,str,num) VALUES (?,?,?,?,?)", (4*i+j, 'hello', None, value, None,))
            elif j == 1:
                db.execute("INSERT INTO " + table + " (fid,key,obj,str,num) VALUES (?,?,?,?,?)", (4*i+j, 'hello', None, None, value,))
            elif j == 2:
                db.execute("INSERT INTO " + table + " (fid,key,obj,str,num) VALUES (?,?,?,?,?)", (4*i+j, 'hello', None, None, value,))
            elif j == 3:
                db.execute("INSERT INTO " + table + " (fid,key,obj,str,num) VALUES (?,?,?,?,?)", (4*i+j, 'hello', value, None, None,))
            
    db.commit()
    ajc.util.time_stop()
    
for table in tables.values():
    table.commit()
    table.close()

